package com.dinner.model;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.dinner.util.PageHelper;

public class OrderDaoHibernate extends HibernateDaoSupport implements OrderDao {

	@Override
	public void saveOrder(Order order) {
		SessionFactory sessionFactory = this.getSessionFactory();
		Session session = sessionFactory.openSession();
		int isolation = -1;
		try {
			isolation = session.connection().getTransactionIsolation();
			session.connection().setTransactionIsolation(
					Connection.TRANSACTION_SERIALIZABLE);
			session.beginTransaction();
			this.getHibernateTemplate().save(order);
			session.getTransaction().commit();
			session.connection().setTransactionIsolation(isolation);
		} catch (Exception e) {
			e.printStackTrace();
			Transaction tx = session.getTransaction();
			if (tx.isActive())
				tx.rollback();
		} finally {
			session.close();
		}

	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Order> getOrderBySeller(String sellerID, byte state) {
		SessionFactory sessionFactory = this.getSessionFactory();
		Session session = sessionFactory.openSession();
		List l = null;
		int isolation = -1;
		try {
			isolation = session.connection().getTransactionIsolation();
			session.connection().setTransactionIsolation(
					Connection.TRANSACTION_SERIALIZABLE);
			session.beginTransaction();
			if (state == 8) {
				l = getHibernateTemplate()
						.find(
								"FROM com.dinner.model.Order orders WHERE "
										+ "orders.sellerID = ? AND orders.orderState = 8",
								sellerID);
			} else {
				l = getHibernateTemplate()
						.find(
								"FROM com.dinner.model.Order orders WHERE "
										+ "orders.sellerID = ? AND orders.orderState < 8",
								sellerID);
			}
			session.getTransaction().commit();
			session.connection().setTransactionIsolation(isolation);
		} catch (Exception e) {
			e.printStackTrace();
			Transaction tx = session.getTransaction();
			if (tx.isActive())
				tx.rollback();
		} finally {
			session.close();
		}
		return l;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Order> getOrderByConsumer(String consumerID, byte state) {
		SessionFactory sessionFactory = this.getSessionFactory();
		Session session = sessionFactory.openSession();
		List l = null;
		int isolation = -1;
		try {
			isolation = session.connection().getTransactionIsolation();
			session.connection().setTransactionIsolation(
					Connection.TRANSACTION_SERIALIZABLE);
			session.beginTransaction();
			if (state == 8) {
				l = getHibernateTemplate()
						.find(
								"FROM com.dinner.model.Order orders WHERE "
										+ "orders.orderState = 8 AND orders.consumerID = ?",
								consumerID);
			} else {
				l = getHibernateTemplate()
						.find(
								"FROM com.dinner.model.Order orders WHERE "
										+ "orders.orderState < 8 AND orders.consumerID = ?",
								consumerID);
			}
			session.getTransaction().commit();
			session.connection().setTransactionIsolation(isolation);
		} catch (Exception e) {
			e.printStackTrace();
			Transaction tx = session.getTransaction();
			if (tx.isActive())
				tx.rollback();
		} finally {
			session.close();
		}
		return l;
	}

	@Override
	public void completeOrder(String consumerID, int orderID,
			byte expectedState, byte newState) {
		SessionFactory sessionFactory = this.getSessionFactory();
		Session session = sessionFactory.openSession();
		int isolation = -1;
		try {
			isolation = session.connection().getTransactionIsolation();
			session.connection().setTransactionIsolation(
					Connection.TRANSACTION_SERIALIZABLE);
			session.beginTransaction();
			org.hibernate.Query query = session.createQuery(
					"UPDATE com.dinner.model.Order orders SET orders.orderState = ? "
							+ "WHERE orders.orderID = ? AND "
							+ "orders.consumerID = ? "
							+ "AND orders.orderState = ?");
			query.setParameter(0, newState).setParameter(1, orderID)
					.setParameter(2, consumerID).setParameter(3, expectedState)
					.executeUpdate();
			session.getTransaction().commit();
			session.connection().setTransactionIsolation(isolation);

		} catch (Exception e) {
			e.printStackTrace();
			Transaction tx = session.getTransaction();
			if (tx.isActive())
				tx.rollback();
		} finally {
			session.close();
		}

	}

	@Override
	public void handleOrder(String sellerID, int orderID, byte expectedState,
			byte newState) {
		SessionFactory sessionFactory = this.getSessionFactory();
		Session session = sessionFactory.openSession();
		int isolation = -1;
		try {
			isolation = session.connection().getTransactionIsolation();
			session.connection().setTransactionIsolation(
					Connection.TRANSACTION_SERIALIZABLE);
			session.beginTransaction();
			org.hibernate.Query query = session
					.createQuery(
							"UPDATE com.dinner.model.Order orders SET orders.orderState = ? "
									+ "WHERE orders.orderID = ? AND orders.orderState = ? AND orders.sellerID = ?");

			query.setParameter(0, newState).setParameter(1, orderID)
					.setParameter(2, expectedState).setParameter(3, sellerID)
					.executeUpdate();
			session.getTransaction().commit();
			session.connection().setTransactionIsolation(isolation);

		} catch (Exception e) {
			e.printStackTrace();
			Transaction tx = session.getTransaction();
			if (tx.isActive())
				tx.rollback();
		} finally {
			session.close();
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Order> getOrderByConsumerAndPage(final String consumerID,
			byte state, final PageHelper p) {
		if (state == 8) {
			return getHibernateTemplate().executeFind(new HibernateCallback() {

				@Override
				public Object doInHibernate(Session s)
						throws HibernateException, SQLException {
					Query query = s
							.createQuery("FROM com.dinner.model.Order orders WHERE orders.orderState = 8 AND orders.consumerID = ?");
					query.setParameter(0, consumerID);
					query.setFirstResult((p.getCurrentPage() - 1)
							* p.getPageSize());
					query.setMaxResults(p.getPageSize());
					List list = query.list();
					query = s
							.createQuery("SELECT COUNT(*) FROM com.dinner.model.Order orders WHERE orders.orderState = 8 AND orders.consumerID = '"
									+ consumerID + "'");
					p.setTotalPage((((Integer) (query.iterate().next()))
							.intValue() - 1)
							/ p.getPageSize() + 1);

					return list;
				}

			});
			/*
			 * .find( "FROM com.dinner.model.Order orders WHERE " +
			 * "orders.orderState = 8 AND orders.consumerID = ?", consumerID);
			 */
		} else {
			return getHibernateTemplate().executeFind(new HibernateCallback() {

				@Override
				public Object doInHibernate(Session s)
						throws HibernateException, SQLException {
					Query query = s
							.createQuery("FROM com.dinner.model.Order orders WHERE orders.orderState < 8 AND orders.consumerID = ?");
					query.setParameter(0, consumerID);
					query.setFirstResult((p.getCurrentPage() - 1)
							* p.getPageSize());
					query.setMaxResults(p.getPageSize());
					List list = query.list();
					query = s
							.createQuery("SELECT COUNT(*) FROM com.dinner.model.Order orders WHERE orders.orderState < 8 AND orders.consumerID = '"
									+ consumerID + "'");
					p.setTotalPage((((Integer) (query.iterate().next()))
							.intValue() - 1)
							/ p.getPageSize() + 1);

					return list;
				}

			});
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Order> getOrderBySellerAndPage(final String sellerID,
			byte state, final PageHelper p) {
		if (state == 8) {
			return getHibernateTemplate().executeFind(new HibernateCallback() {
				@Override
				public Object doInHibernate(Session s)
						throws HibernateException, SQLException {
					Query query = s
							.createQuery("FROM com.dinner.model.Order orders WHERE orders.orderState = 8 AND orders.sellerID = ?");
					query.setParameter(0, sellerID);
					query.setFirstResult((p.getCurrentPage() - 1)
							* p.getPageSize());
					query.setMaxResults(p.getPageSize());
					List list = query.list();
					query = s
							.createQuery("SELECT COUNT(*) FROM com.dinner.model.Order orders WHERE orders.orderState = 8 AND orders.sellerID = '"
									+ sellerID + "'");
					p.setTotalPage((((Integer) (query.iterate().next()))
							.intValue() - 1)
							/ p.getPageSize() + 1);

					return list;
				}

			});
			/*
			 * .find( "FROM com.dinner.model.Order orders WHERE " +
			 * "orders.sellerID = ? AND orders.orderState = 8", sellerID);
			 */
		} else {
			return getHibernateTemplate().executeFind(new HibernateCallback() {
				@Override
				public Object doInHibernate(Session s)
						throws HibernateException, SQLException {
					Query query = s
							.createQuery("FROM com.dinner.model.Order orders WHERE orders.orderState < 8 AND orders.sellerID = ?");
					query.setParameter(0, sellerID);
					query.setFirstResult((p.getCurrentPage() - 1)
							* p.getPageSize());
					query.setMaxResults(p.getPageSize());
					List list = query.list();
					query = s
							.createQuery("SELECT COUNT(*) FROM com.dinner.model.Order orders WHERE orders.orderState < 8 AND orders.sellerID = '"
									+ sellerID + "'");
					p.setTotalPage((((Integer) (query.iterate().next()))
							.intValue() - 1)
							/ p.getPageSize() + 1);

					return list;
				}

			});
		}
	}
}
